use std::str::FromStr;

use crate::db::connector::get_connection;
use crate::error::AppError;
use crate::schemas::base::{FilterFunc, FilterItem, FilterValue, ListQuery, ListResult};
use sea_orm::sea_query::Expr;
use sea_orm::{ColumnTrait, EntityTrait, FromQueryResult, PaginatorTrait, QueryFilter, QueryOrder};

pub trait FilterBuilder<E: EntityTrait> {
    fn apply_filters(self, filters: Vec<FilterItem>) -> Result<Self, AppError>
    where
        E::Column: ColumnTrait + FromStr,
        Self: QueryFilter + Sized,
    {
        let mut current_query = self;
        for filter in filters {
            let column_enum = E::Column::from_str(&filter.field).map_err(|_| {
                AppError::Custom(format!("Invalid filter field: '{}'.", filter.field))
            })?;

            match filter.func {
                FilterFunc::IN => match filter.value {
                    Some(FilterValue::Array(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).is_in(val));
                    }
                    Some(FilterValue::List(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).is_in(val))
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "IN filter for field '{}' requires an array value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::EQ => match filter.value {
                    Some(FilterValue::String(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).eq(val));
                    }
                    Some(FilterValue::Number(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).eq(val));
                    }
                    Some(FilterValue::Bool(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).eq(val));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "EQ filter for field '{}' requires a single value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::NE => match filter.value {
                    Some(FilterValue::String(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).ne(val));
                    }
                    Some(FilterValue::Number(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).ne(val));
                    }
                    Some(FilterValue::Bool(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).ne(val));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "NE filter for field '{}' requires a single value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::GT => match filter.value {
                    Some(FilterValue::String(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).gt(val));
                    }
                    Some(FilterValue::Number(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).gt(val));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "GT filter for field '{}' requires a single value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::GTE => match filter.value {
                    Some(FilterValue::Number(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).gte(val));
                    }
                    Some(FilterValue::String(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).gte(val));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "GTE filter for field '{}' requires a single value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::LT => match filter.value {
                    Some(FilterValue::String(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).lt(val));
                    }
                    Some(FilterValue::Number(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).lt(val));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "LT filter for field '{}' requires a single value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::LTE => match filter.value {
                    Some(FilterValue::String(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).lte(val));
                    }
                    Some(FilterValue::Number(val)) => {
                        current_query = current_query.filter(Expr::col(column_enum).lte(val));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "LTE filter for field '{}' requires a single value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::LIKE => match filter.value {
                    Some(FilterValue::String(s)) => {
                        current_query =
                            current_query.filter(Expr::col(column_enum).like(format!("%{}%", s)));
                    }
                    _ => {
                        return Err(AppError::Custom(format!(
                            "LIKE filter for field '{}' requires a single string value.",
                            filter.field
                        )))
                    }
                },
                FilterFunc::NULL => {
                    current_query = current_query.filter(Expr::col(column_enum).is_null());
                }
                FilterFunc::NotNull => {
                    current_query = current_query.filter(Expr::col(column_enum).is_not_null());
                }
            }
        }
        Ok(current_query)
    }
}

impl<E: EntityTrait> FilterBuilder<E> for sea_orm::Select<E> {}

pub async fn list<Entity, M>(
    db_identifier: &str,
    params: ListQuery,
) -> crate::error::Result<ListResult<Entity::Model>, AppError>
where
    Entity: EntityTrait<Model = M>,
    M: FromQueryResult + Sized + Sync + Send,
{
    let db = get_connection(db_identifier)?;
    // 查询句柄
    let mut cur = Entity::find();
    // 处理筛选逻辑
    if let Some(filters) = params.filters {
        cur = cur.apply_filters(filters)?;
    }
    // 处理排序逻辑
    if let Some(order_bys) = params.order_by {
        for order_by in order_bys {
            let column_enum = Entity::Column::from_str(&order_by.field).map_err(|_| {
                AppError::Custom(format!("Invalid order_by field '{}'.", order_by.field))
            })?;
            if order_by.desc {
                cur = cur.order_by_desc(column_enum);
            } else {
                cur = cur.order_by_asc(column_enum);
            }
        }
    }
    // 处理分页逻辑
    let paginator = cur.paginate(&db, params.pagination.page_size);

    // 总数
    let total = paginator.num_items().await?;

    let result = paginator
        .fetch_page(params.pagination.page - 1)
        .await
        .map_err(|e| AppError::Custom(format!("Failed to find records: {:?}", e)))?;
    Ok(ListResult { result, total })
}

pub async fn all<Entity>(db_identifier: &str) -> crate::error::Result<Vec<Entity::Model>, AppError>
where
    Entity: EntityTrait,
{
    let db = get_connection(db_identifier)?;

    Entity::find()
        .all(&db)
        .await
        .map_err(|e| AppError::Custom(format!("Failed to find records: {:?}", e)))
}

pub async fn all_by_filter<Entity>(
    db_identifier: &str,
    filters: Option<Vec<FilterItem>>,
) -> crate::error::Result<Vec<Entity::Model>, AppError>
where
    Entity: EntityTrait,
{
    let db = get_connection(db_identifier)?;

    // 查询句柄
    let mut cur = Entity::find();

    // 处理筛选逻辑
    if let Some(filters) = filters {
        cur = cur.apply_filters(filters)?;
    }

    cur.all(&db)
        .await
        .map_err(|e| AppError::Custom(format!("Failed to find records: {:?}", e)))
}

pub async fn one<Entity>(
    db_identifier: &str,
    filters: Option<Vec<FilterItem>>,
) -> crate::error::Result<Entity::Model, AppError>
where
    Entity: EntityTrait,
{
    let db = get_connection(db_identifier)?;

    let mut cur = Entity::find();

    if let Some(filters) = filters {
        cur = cur.apply_filters(filters)?;
    }

    let data = cur
        .one(&db)
        .await
        .map_err(|e| AppError::Custom(format!("Failed to find records: {:?}", e)))?;
    if let Some(data) = data {
        Ok(data)
    } else {
        Err(AppError::Custom("No data was found.".to_string()))
    }
}
